（优化篇）MySQL 优化之索引

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 5.6k | 阅读时长 ≈ 8 分钟

序言

  为了加快数据查询的速度，通常会使用到索引，那么什么是索引呢？它底层是如何构成的呢？又该如何正确使用呢？

什么是索引？

  MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。
  在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

  简单来讲，索引是数据结构。

  首先来看个没有索引的数据表：

  由于上表中并无索引，若我们想找到一个公司的数据行，需要检查数据表的每一行以确定其是否与期望值匹配，这将完全扫描此数据表。如果数据很大，但是仅有几个记录与搜索条件匹配，那么工作过程就很慢，效率就会很低。
  现将上表的company_num数据列添加索引，如下图：

  现在，索引包含了数据表中每一个数据行的项，且根据company_num的值来分类，因此，使用索引后，假设现要查找公司编号为 13 的所有数据行，则会开始扫描索引并将找到 3 个匹配的数据行。
  当继续扫描到公司编号为 14 的数据行（ 14 高于需要搜寻的值）时会发现：由于索引值是经过分类的，将知道不会再有与 13 相匹配的内容了，决定不再扫描。

  由此可知索引能提高查询效率的一个原因就是其可以得知匹配的数据行在什么位置结束，从而跳过其他部分。另一个原因则是定位算法的使用。

索引种类

按存储结构划分

  索引按存储结构可划分为：

  • 聚簇索引
  • 非聚簇索引

聚簇索引（聚集索引）

  聚簇索引中，聚簇指的是数据行和相邻的键值紧凑地存储在一起。

  因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

  对聚簇索引来说，需要依赖一个唯一值确定，所以：

  • 若表已设主键，则主键就是聚簇索引
  • 若表未设主键，则会默认选择第一个唯一（UNIQUE）且NOT NULL的列作为聚簇索引
  • 若以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引

  在 MySQL 中 InnoDB 数据引擎默认主索引就是聚集索引，聚集索引的索引结构中不仅包含了键值（一般为主键值），还相应地包含了表的数据。

  InnoDB 中必须要有一个聚集索引，而聚集索引在基于主键创建的，所以 InnoDB 的表必须要有主键，若没有显示指定主键，Mysql 会自动创建一个隐式主键且同时建立一个唯一的聚集索引。
  InnoDB 除了主索引（聚集索引）之外的索引都是以辅助索引（非聚簇索引）的形式存在，辅助索引每次检索的时候都先找到主索引再找到数据（回表）;

非聚簇索引（二级索引）

  与聚簇索引不同，非聚簇索引中的数据域不存储具体的数据，而是存储聚簇索引的主键值。

  与聚簇索引不同，一个表可以有不止一个非聚簇索引。

区别

  聚集索引与非聚集索引的区别是：叶子节点是否存放一整行记录

  • 对于聚簇索引表来说，表数据是和主键一起存储的，主键索引的叶子结点存储行数据（包含了主键值），即叶子节点上的数据是主键与具体记录（数据内容）
  • 对于非聚簇索引表来说，并没有存储表数据，存储的是聚簇索引的主键，数据需要去聚簇索引中查询

按数据结构划分

  索引按数据结构可划分为：

  • FULLTEXT 索引：是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene Solr，ES，常用于全文检索场景
  • SPATIAL 索引：MySQL 中只适用只适用 MyISAM
  • HASH 索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，只能用于对等比较，不支持范围查询，无法排序数据，MySQL 中只适用 MEMORY引擎
  • R-tree索引：空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
  • B+TREE索引：最常见的索引类型，大部分引擎都支持B+树索引，MySQL 默认使用

其他划分

唯一索引

  唯一索引限制了在表对应的唯一索引列上的值是唯一不可重复的，其具有如下特点：

  • 唯一唯一索引自然不能重复
  • 一个表可以创建多个唯一索引
  • 唯一索引的值允许为 NULL
  • 允许多个列建立唯一索引的组合（复合索引），这意味着那几个数据列的值的组合不能重复

注意哦：主键索引属于唯一索引的一个特殊种类，一个表的某列创建主键索引后会具备唯一索引的特点同时还会对该列生成主键约束，主键唯一且不允许有重复的值，并且不能为 NULL 值。

普通索引

  默认创建的索引就是普通索引，普通索引是非唯一的，所以允许索引项出现重复。

联合（复合）索引

  联合（复合）索引指的是对多个列创建一种多列的索引，这种索引可能是唯一索引，也可能是普通索引（非唯一索引）。

不同存储引擎的索引特性

  MySQL 提供了多种灵活的索引创建方法：

  • 可以为单个数据列创建索引，亦可以为多个数据列创建复合索引
  • 索引可以只包含独一无二的值，也可以包含重复的值
  • 可以为同一个数据表创建多个索引并分别利用它们来优化基于不同数据列的查询

  而不同的存储引擎对索引功能的支持情况存在如下差异：

特性 InnoDB MyISAM MEMOry MERGE NDB
存储限制 64 TB 有 有 无 有
锁机制 行锁（适合高并发） 表锁 表锁 表锁 行锁
BTREE 索引 支持 支持 支持 支持 支持
HASH 索引 支持
FULLTEXT 索引 支持（5.6 版本后） 支持
缓存 支持 支持 支持 支持 支持
数据可压缩 支持
空间使用 高 低 N/A 低 低
内存使用 高 低 中 低 高
批量插入速度 低 高 高 高 高

注意哦：其中只有 InnoDB 引擎支持事务和外键。

  对于不同的存储引擎，索引实现的细节不同：

  • InnoDB：将所有数据表的数据和索引存储在一个表空间中，亦可为每个数据表分别创建一个表空间
  • MyISAM：将数据表的数据行存储在数据文件中，索引值则在索引文件中

如何选择存储引擎？

  在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎：

  • InnoDB ：是 MySQL 默认的存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎时比较合适的选择。InnoDB 存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保食物的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 是最合适的选择。
  • MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个引擎时非常合适的。
  • MEMORY ： 将所有的数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供极快的访问。MEMORY 的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。

注意哦:对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合

索引语法

创建索引

  创建索引有 2 种方式：

  • 在建表时创建索引：
    • CREATE INDEX 索引名 ON 表名(列名[前缀长度])
    • CREATE PRIMARY KEY 索引名 ON 表名(列名[前缀长度])
    • CREATE UNIQUE KEY 索引名 ON 表名(列名[前缀长度])
    • CREATE FULLTEXT 索引名 ON 表名(列名[前缀长度])
    • CREATE SPATIAL 索引名 ON 表名(列名[前缀长度])
    • CREATE HASH 索引名 ON 表名(列名[前缀长度])
  • 给现有表添加索引：
    • ALTER TABLE 表名 ADD INDEX 索引名(列名[前缀长度]，列名[前缀长度]...)
    • ALTER TABLE 表名 ADD PRIMARY KEY 索引名(列名[前缀长度])
    • ALTER TABLE 表名 ADD UNIQUE KEY 索引名(列名[前缀长度])
    • ALTER TABLE 表名 ADD FULLTEXT 索引名(列名[前缀长度])
    • ALTER TABLE 表名 ADD SPATIAL 索引名(列名[前缀长度])
    • ALTER TABLE 表名 ADD HASH 索引名(列名[前缀长度])

注意哦：CREATE 创建索引时索引名不可选且不能创建多个索引，而ALTER可以。其中[前缀长度]为可选参数。

删除索引

  删除索引也有 2 种方式：

  • DROP INDEX 索引名 ON 表名
  • ALTER TABLE 表名 DROP INDEX 索引名

索引优劣

优

  • 提高查询效率，降低数据库的 IO 成本，降低 CPU 的消耗

缺

  • 索引存在失效情况
  • 索引种类繁多，需要开发人员分析业务选择合适的索引
  • 索引会占据磁盘空间，多个索引则会占据更大的空间
  • 对带索引的数据列执行插入、删除及修改操作时执行速度会降低，因为这些操作时所有索引都得跟着改变

索引失效情况

  并不是每次查询都能通过索引走捷径，因为索引还存在以下失效情况：

  • 复合索引不遵循最左前缀法则时
  • 复合索引范围查询时右边的列不走索引
  • 在索引列上使用运算或函数时
  • 字符串索引不加单引号时
  • 用 OR 分割开的条件，若 OR 前的条件列中有索引，而后的列无索引
  • 以 % 开头的 LIKE 模糊查询（可通过覆盖索引解决）
  • 若 MySQL 评估全表扫描比索引更快，则不使用索引
  • IN 走索引，NOT IN 不走索引

如何挑选索引？

  我们在创建索引时该选择为哪些列添加呢？总结如下

  • 尽量为用作搜寻、分类或分组的数据列创建索引，不要为作为输出显示的数据列创建索引。换而言之，根据SELECT 输出的数据列最好不要创建索引，而适合索引的数据列包括：

    • 在WHERE子句出现的
    • 在连接（联结）子句给出的
    • ORDER BY或GROUP BY子句出现的
      1
      2
      3
      4
      5
      6
      7
      SELECT 
          col_a    // 最好不要创建索引
      FROM
          t1 LEFT JOIN t2
          ON b1.col_b = b2.col_c    // 适合创建索引
      WHERE 
          col_d = expr;    // 适合创建索引

  • 尽量选择非重复值多的列

  • 尽量选择数值更小的列
  • 选择最左边的前缀
  • 尽量使用复合索引，而少使用单列索引
1
2
3
4
5
6
7
8
9
10
11
-- 创建复合索引相当于创建了多个索引
CREATE INDEX idx_name_status_address ON t_user(name,status,address)
-- 上面相当于创建了 3 个索引
-- name
-- name + status
-- name + status + address

-- 而创建单列索引时数据库会选择一个最优的索引来使用，不会使用全部索引
CREATE INDEX idx_name ON t_user(name,status,address)
CREATE INDEX idx_status ON t_user(status)
CREATE INDEX idx_address ON t_user(address)

  这样总结可能有点混乱，其实也可以细分为：

  • 什么场景使用索引？
  • 什么场景不用索引？

什么场景使用索引？

  • 用于连接的列（如 LEFT JOIN … ON ... ）
  • 经常要用于查询的列（如 WHERE id = ?）
  • 经常要用于排序（ORDER BY）和分组（GROUP BY）的列，因为索引已经排好序了;
  • 具有值唯一性限制的列，比如说主键、用户名
  • 利用最左前缀，N 个列组合而成的复合索引就相当于参加了 N 个索引，如果查询时 WHERE 子句中使用了组成该索引的前几个字段，那么这条查询 SQL 可以利用组合索引来提升查询效率，如
    1
    2
    3
    4
    5
    6
    7
    -- 创建复合索引
    CREATE INDEX idx_name_email_status ON t_user(name,email,status)

    -- 相当于
       --   对 name 创建索引；
       --   对 name, email 创建索引；
       --   对 name, email , status 创建索引；

什么场景不用索引？

  • 数据更新性能比查询性能要求要高的情况下不要使用索引，因为数据的更新的同时索引也要进行维护和更新（加了索引查询快但更新就会慢）;
  • 不要盲目的给表建太多索引，因为索引本身的存储也要占用存储空间，一旦更新操作频繁反而降低新性能;
  • 不要给不经常使用的列建索引，不怎么查询还建索引干嘛;
  • 不要给高重复值的列建索引，索引本身就是为了提高查询速度，然而数据值高度重复，数据区别性不高，索引起不了效果）（比如说：性别）;
  • 不要给 img、text 等数据类型使用索引，因为这种字段一般使用很少，且其数据量太大;

补充： Mysql 的 B+Tree 索引

  在 MySQL 中索引默认使用的数据结构是优化后的 B+Tree 。
  对原 B+Tree 而言，只有叶子节点保存着 key 信息，查询任何 key 都要从根节点走到叶子节点，与 BTree 相比其查询效率更加稳定。而 MySQL 在原 B+Tree 的基础上，增加了一个指向相邻叶子结点的链表指针，形成了带有顺序指针的 B+Tree，这提高了区间访问（范围查找）的性能。

疑问

问：为什么不直接将数据行进行分类，从而省掉索引呢？
答：数据表可能不止一个索引，如顾客表中在可以使用 ID 号作为索引时，还可以使用电话号作为索引，或者更多的索引。

为什么 lnnoDB 存储引擎选择使用 B+tree 索引结构?

  • 相对于二叉树，层级更少，搜索效率高;
  • 相对于 B-tree，B-tree 无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
  • 相对 Hash 索引，B+tree 支持范围匹配及排序操作;

参考

  • Paul DuBois. MySQL 技术内幕 [M]. 人民邮电出版社, 2011
0%